using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using DTO;
using System.Data.OleDb;

namespace DAO
{
    public class ChiTietPhieuKhamDAO
    {
        DataProvider Provider;
        public ChiTietPhieuKhamDAO()
        {
            Provider = new DataProvider();
        }

        public DataTable LayMaChiTietPhieuKham()
        {
            Provider.Connect();
            string str = "Select MaChiTietPK From CHITIETPK Where MaChiTietPK >= all (Select MaChiTietPK From ChiTietPK)";
            DataTable dt = new DataTable();
            OleDbDataAdapter da;
            da = new OleDbDataAdapter(str, Provider.cn);
            da.Fill(dt);
            Provider.Disconnect();
            return dt;
        }

        public DataTable LayBangChiTietPhieuKham(string maPK)
        {
            Provider.Connect();
            string str = "select ctpk.MaChiTietPK,t.TenLoaiThuoc as TenLoaiThuoc, dv.TenDV, ctpk.SoLuong, dg.DonGia, cd.TenCD, ctpk.ThanhTien  from ChiTietPK ctpk, THUOC t, DONVI dv, CACHDUNG cd, DONGIA dg where ctpk.MaLoaiThuoc = t.MaLoaiThuoc and ctpk.MaDV = dv.MaDV and ctpk.MaCD = cd.MaCD and dg.MaLoaiThuoc = t.MaLoaiThuoc and dg.MaDonVi = dv.MaDV and ctpk.MaPK = '" + maPK + "'";
            DataTable dt = new DataTable();
            OleDbDataAdapter da;
            da = new OleDbDataAdapter(str, Provider.cn);
            da.Fill(dt);
            Provider.Disconnect();
            return dt;
        }

        public DataTable LayDonGiaThuoc(string maLoaiThuoc, string maDonVi)
        {
            Provider.Connect();
            string str = "Select dg.DonGia from DONGIA dg where dg.MaLoaiThuoc = '" + maLoaiThuoc + "' and dg.MaDonVi = '" + maDonVi + "'";
            DataTable dt = new DataTable();
            OleDbDataAdapter da = new OleDbDataAdapter(str, Provider.cn);
            da.Fill(dt);
            Provider.Disconnect();
            return dt;
        }

        public DataTable TienThuoc(string maPhieuKham)
        {
            Provider.Connect();
            string str = "Select sum(ctpk.ThanhTien) as TienThuoc from CHITIETPK ctpk where ctpk.MaPK = '" + maPhieuKham + "'";
            DataTable dt = new DataTable();
            OleDbDataAdapter da = new OleDbDataAdapter(str, Provider.cn);
            da.Fill(dt);
            Provider.Disconnect();
            return dt;
        }

        public void ThemChiTietPhieuKham(ChiTietPhieuKhamDTO ctpkDto)
        {
            Provider.Connect();
            string str;
            str = "Insert into ChiTietPK(MaPK, MaLoaiThuoc, MaDV, MaCD, SoLuong, ThanhTien, MaChiTietPK) values (?, ?, ?, ?, ?, ?, ?)";

            OleDbCommand cmd = new OleDbCommand(str, Provider.cn);
            cmd.Parameters.Add("@MaPK", OleDbType.WChar);
            cmd.Parameters.Add("@MaLoaiThuoc", OleDbType.WChar);
            cmd.Parameters.Add("@MaDV", OleDbType.WChar);
            cmd.Parameters.Add("@MaCD", OleDbType.WChar);
            cmd.Parameters.Add("@SoLuong", OleDbType.Integer);
            cmd.Parameters.Add("@ThanhTien", OleDbType.LongVarWChar);
            cmd.Parameters.Add("@MaChiTietPK", OleDbType.WChar);

            cmd.Parameters["@MaPK"].Value = ctpkDto.MaPK;
            cmd.Parameters["@MaLoaiThuoc"].Value = ctpkDto.MaLoaiThuoc;
            cmd.Parameters["@MaDV"].Value = ctpkDto.MaDV;
            cmd.Parameters["@MaCD"].Value = ctpkDto.MaCD;
            cmd.Parameters["@SoLuong"].Value = ctpkDto.SoLuong;
            cmd.Parameters["@ThanhTien"].Value = ctpkDto.ThanhTien;
            cmd.Parameters["@MaChiTietPK"].Value = ctpkDto.MaChiTietPK;

            cmd.ExecuteNonQuery();
            Provider.Disconnect();

        }


        public void XoaChiTietPhieuKham(string maChiTietPK)
        {
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            Provider.Connect();
            // B3: Tao chuoi strSQL thao tac CSDL
            string str;
            str = "Delete From ChiTietPK Where MaChiTietPK = ?";
            OleDbCommand cmd = new OleDbCommand(str, Provider.cn);
            cmd.Parameters.Add("@MaChiTietPK", OleDbType.Char);
            cmd.Parameters["@MaChiTietPK"].Value = maChiTietPK;
            cmd.ExecuteNonQuery();
            // B5: Dong ket noi CSDL
            Provider.Disconnect();
        }
        public void SuaChiTietPhieuKham(ChiTietPhieuKhamDTO ctpkDto)
        {
            Provider.Connect();
            // B3: Tao chuoi strSQL thao tac CSDL
            string str;
            str = "Update ChiTietPK Set MaPK = ?,MaLoaiThuoc=?, MaDV = ?, MaCD = ?, SoLuong = ?, ThanhTien= ? Where MaChiTietPK = ? ";
            OleDbCommand cmd = new OleDbCommand(str, Provider.cn);

            cmd.Parameters.Add("@MaPK", OleDbType.WChar);
            cmd.Parameters.Add("@MaLoaiThuoc", OleDbType.WChar);
            cmd.Parameters.Add("@MaDV", OleDbType.WChar);
            cmd.Parameters.Add("@MaCD", OleDbType.WChar);
            cmd.Parameters.Add("@SoLuong", OleDbType.LongVarWChar);
            cmd.Parameters.Add("@ThanhTien", OleDbType.LongVarWChar);
            cmd.Parameters.Add("@MaChiTietPK", OleDbType.WChar);

            cmd.Parameters["@MaPK"].Value = ctpkDto.MaPK;
            cmd.Parameters["@MaLoaiThuoc"].Value = ctpkDto.MaLoaiThuoc;
            cmd.Parameters["@MaDV"].Value = ctpkDto.MaDV;
            cmd.Parameters["@MaCD"].Value = ctpkDto.MaCD;
            cmd.Parameters["@SoLuong"].Value = ctpkDto.SoLuong;
            cmd.Parameters["@ThanhTien"].Value = ctpkDto.ThanhTien;
            cmd.Parameters["@MaChiTietPK"].Value = ctpkDto.MaChiTietPK;

            cmd.ExecuteNonQuery();
            Provider.Disconnect();
        }


        public DataTable TimKiemTheoMaChiTietPK(string maChiTietPK)
        {
            Provider.Connect();
            DataTable dt = new DataTable();
            //B3: Tao chuoi strSQL thao tac CSDL
            string str = "Select * From ChiTietPK Where MaChiTietPK = " + "'" + maChiTietPK + "'";
            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(str, Provider.cn);

            cmd.Parameters.Add("@MaChiTietPK", OleDbType.WChar);
            cmd.Parameters["@MaChiTietPK"].Value = maChiTietPK;

            OleDbDataAdapter da;
            da = new OleDbDataAdapter(str, Provider.cn);
            da.Fill(dt);
            Provider.Disconnect();
            return dt;
        }
    }
}
